package flights.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import flights.bi.Flight_Seat;

public class FlightSeatHandler {

	public static List<Flight_Seat> getAllFlight_SeatsByFlight(int nFlightId) throws SQLException
	{
		List<Flight_Seat> Flight_Seats = new ArrayList<Flight_Seat>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT flight_seat.* FROM flight_seat LEFT JOIN ticket ON (ticket.seat_id = flight_seat.seat_id) WHERE flight_id=? AND ticket.seat_id IS NULL";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlightId);
			ResultSet rs = st.executeQuery();
			Flight_Seats = extractFlight_SeatsFromRS(rs);
		} catch (SQLException ex) {
			throw new SQLException(ex);
		}
		
		conn.close();
		return Flight_Seats;
	}
	
	public static List<Flight_Seat> getAllSeatsByBooking(int nBookingId) throws SQLException
	{
		List<Flight_Seat> Flight_Seats = new ArrayList<Flight_Seat>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT fs.* FROM ticket t, flight_seat fs WHERE t.booking_id=? and t.seat_id = fs.seat_id";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nBookingId);
			ResultSet rs = st.executeQuery();
			Flight_Seats = extractFlight_SeatsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return Flight_Seats;
	}
	
	public static Flight_Seat getFlight_SeatByID(int nFlight_SeatID) throws SQLException
	{
		List<Flight_Seat> Flight_Seats = new ArrayList<Flight_Seat>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM flight_seat where seat_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlight_SeatID);
			ResultSet rs = st.executeQuery();
			Flight_Seats = extractFlight_SeatsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		
		if (!Flight_Seats.isEmpty())
		{
			return Flight_Seats.get(0);
		}
		else
		{
			return null;
		}
		
	}
	
	public static List<Flight_Seat> getFlight_SeatByParams(int flight_id,	String seats_class) throws SQLException
	{
		List<Flight_Seat> Flight_Seats = new ArrayList<Flight_Seat>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM flight_seat where flight_id=? and class=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,flight_id);
			st.setString(2,seats_class);
			ResultSet rs = st.executeQuery();
			Flight_Seats = extractFlight_SeatsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		
		if (!Flight_Seats.isEmpty())
		{
			return Flight_Seats;
		}
		else
		{
			return null;
		}
		
	}
	
	public static int deleteFlight_SeatByID(int nFlight_SeatID) throws SQLException
	{
		int nRows = 0;
		nRows += TicketHandler.deleteTicketBySeat(nFlight_SeatID);
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM flight_seat where Seat_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlight_SeatID);
			nRows += st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;
	}
	
	
	public static int deleteFlight_SeatByFlightID(int nFlightID) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM flight_seat where Flight_id=?";
		int nRows = 0;
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlightID);
			nRows = st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;
	}
	
	public static int upsertFlight_Seat(Flight_Seat Flight_Seat) throws SQLException
	{
		List<Flight_Seat> singleFlight_Seat = new ArrayList<Flight_Seat>();
		singleFlight_Seat.add(Flight_Seat);
		int nRetVal = upsertFlight_Seat(singleFlight_Seat);
		return nRetVal;
	}
	
	public static int upsertFlight_Seat(List<Flight_Seat> Flight_Seats) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		ResultSet generatedKeys = null;
		int nRowsAffected = 0;
		for (int i = 0; i < Flight_Seats.size(); i++) {
			if (getFlight_SeatByID(Flight_Seats.get(i).getSeat_id()) != null)
			{
				String sql = "update flight_seat set Flight_Id=? , Class=? , `row`=?, `column`=?, Cost=?, Reserved=? where Seat_id = ?";
				try (PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)) {
					st.clearParameters();
					st.setInt(1,Flight_Seats.get(i).getFlight_id());
					st.setString(2, Flight_Seats.get(i).getSeat_class());
					st.setInt(3, Flight_Seats.get(i).getRow());
					st.setString(4, String.valueOf(Flight_Seats.get(i).getColumn()));
					//st.setInt(4, Flight_Seats.get(i).getColumn());
					st.setInt(5, Flight_Seats.get(i).getCost());
					st.setBoolean(6, Flight_Seats.get(i).isIs_reserved());
					st.setInt(7, Flight_Seats.get(i).getSeat_id());
					nRowsAffected += st.executeUpdate();
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
					throw new SQLException(ex.getMessage());
				}
			}
			else // new Flight_Seat
			{
				String sql = "insert into flight_seat(Flight_Id,Class,`row`,`column`,Cost,Reserved) values (?,?,?,?,?,?)";
				try (PreparedStatement st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
					st.clearParameters();
					st.setInt(1,Flight_Seats.get(i).getFlight_id());
					st.setString(2, Flight_Seats.get(i).getSeat_class());
					st.setInt(3, Flight_Seats.get(i).getRow());
					st.setString(4, String.valueOf(Flight_Seats.get(i).getColumn()));
					st.setInt(5, Flight_Seats.get(i).getCost());
					st.setBoolean(6, Flight_Seats.get(i).isIs_reserved());
					nRowsAffected += st.executeUpdate();
					generatedKeys = st.getGeneratedKeys();
					if (generatedKeys.next()) {
			            nRowsAffected = generatedKeys.getInt(1);
			        } else {
			            throw new SQLException("Creating user failed, no generated key obtained.");
			        }
					
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
		}
		
		conn.close();
		return nRowsAffected;
	}
	
	private static List<Flight_Seat> extractFlight_SeatsFromRS(ResultSet resultSet) throws SQLException {
		List<Flight_Seat> Flight_Seats = new ArrayList<Flight_Seat>();
		
		while (resultSet.next()) {
			int Flight_Seat_id=resultSet.getInt("Seat_id");
			int Flight_Id=resultSet.getInt("Flight_Id");
			String seat_Class=resultSet.getString("Class");
			int Row=resultSet.getInt("Row");
			char Column=(resultSet.getString("Column").charAt(0));
			int Cost=resultSet.getInt("Cost");
			boolean is_reserved=resultSet.getBoolean("Reserved");
			
			Flight_Seat seat = new Flight_Seat(Flight_Seat_id, Flight_Id, seat_Class, Row, Column, Cost, is_reserved);
			Flight_Seats.add(seat);
		}
		
		return Flight_Seats;
	}

	public static List<Integer> generateSeats(int flight_id, int seatCost,
			String seats_class, int seatsCount) throws SQLException 
	{
		List<Integer> generatedKeys = new ArrayList<Integer>();
		for (int i = 0; i < seatsCount; i++) {
			Flight_Seat seat = new Flight_Seat(-1, flight_id, seats_class, 0, 'A', seatCost, false);
			generatedKeys.add(FlightSeatHandler.upsertFlight_Seat(seat));
		}
		
		return generatedKeys;
	}

	public static List<Integer> getSeatsByParams(int flight_id,
			String seats_class, int seatsCount)  throws SQLException{
		// TODO Auto-generated method stub
		List<Integer> seatIDs = new ArrayList<Integer>();
		List<Flight_Seat> seats = new ArrayList<Flight_Seat>();
		seats = getFlight_SeatByParams(flight_id, seats_class);
		
		for (int i = 0; i < seatsCount; i++) {
			seatIDs.add(seats.get(i).getSeat_id());
		}
		
		return seatIDs;
	}
}
